package com.myaihui.service.impl;

import com.belonk.commons.util.date.DateUtils;
import com.myaihui.domain.BaseBillItem;
import com.myaihui.domain.PMBill;
import com.myaihui.domain.PMBillItem;
import com.myaihui.domain.SysUser;
import com.myaihui.foundation.constant.SysConst;
import com.myaihui.foundation.model.PagerQuery;
import com.myaihui.foundation.query.PMBillQuery;
import com.myaihui.repository.PMBillRepository;
import com.myaihui.service.PMBillItemService;
import com.myaihui.service.PMBillService;
import com.myaihui.util.LoginUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.Predicate;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * Created by sun on 2018/9/8.
 *
 * @author sunfuchang03@126.com
 * @version 1.0
 * @since 1.0
 */
@Service
public class PMBillServiceImpl extends BasePageableServiceImpl<PMBill, String> implements PMBillService {
    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     *
     * Static fields/constants/initializer
     *
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    private static Logger log = LoggerFactory.getLogger(PMBillServiceImpl.class);
    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     *
     * Instance fields
     *
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    private PMBillRepository billRepository;
    @Autowired
    private PMBillItemService billItemService;

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     *
     * Constructors
     *
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    @Autowired
    public PMBillServiceImpl(PMBillRepository billRepository) {
        super(billRepository);
        this.billRepository = billRepository;
    }

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     *
     * Public Methods
     *
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    @Override
    public Specification<PMBill> buildSpecification(PagerQuery query) {
        Assert.notNull(query, "Query must not be null.");
        PMBillQuery billQuery = (PMBillQuery) query;
        return (root, cq, cb) -> {
            String userName = billQuery.getLiveUserName();
            String userMobile = billQuery.getLiveUserMobile();
            String houseNumber = billQuery.getHouseNumber();
            Date paidStartTime = billQuery.getPaidStartTime();
            Date paidEndTime = billQuery.getPaidEndTime();
            Date releaseStartTime = billQuery.getReleaseStartTime();
            Date releaseEndTime = billQuery.getReleaseEndTime();
            String propertyMgrId = billQuery.getPropertyMgrId();
            String orderField = billQuery.getOrderField();
            int status = billQuery.getStatus();

            List<Predicate> predicates = new LinkedList<>();
            predicates.add(cb.equal(root.get("status"), status));
            if (StringUtils.hasLength(propertyMgrId)) {
                predicates.add(cb.equal(root.get("createUserPlatformId"), propertyMgrId));
                predicates.add(cb.equal(root.get("createUserPlatformType"), SysConst.SubjectType.PROPERTY));
            }
            if (StringUtils.hasLength(userName)) {
                predicates.add(cb.like(root.get("userName"), "%" + userName + "%"));
            }
            if (StringUtils.hasLength(userMobile)) {
                predicates.add(cb.like(root.get("userMobile"), "%" + userMobile + "%"));
            }
            if (StringUtils.hasLength(houseNumber)) {
                predicates.add(cb.like(root.get("houseNumber"), "%" + houseNumber + "%"));
            }
            if (releaseStartTime != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("releaseTime"), releaseStartTime));
            }
            if (releaseEndTime != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("releaseTime"), releaseEndTime));
            }
            if (paidStartTime != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("paidTime"), paidStartTime));
            }
            if (paidEndTime != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("paidTime"), paidEndTime));
            }
            if (predicates.size() > 0) {
                if (StringUtils.hasLength(orderField)) {
                    cq.orderBy(cb.desc(root.get(orderField)));
                } else {
                    cq.orderBy(cb.desc(root.get("created")));
                }
                cq.where(predicates.toArray(new Predicate[predicates.size()]));
            }
            return cq.getRestriction();
        };
    }

    @Override
    public Map<String, Object> importFromExcel(Sheet sheet) {
        Assert.notNull(sheet, "sheet must not be null");
        List<Integer> failedRowNumbers = new LinkedList<>();

        Map<String, PMBill> billMap = parseBillData(sheet, failedRowNumbers);
        saveToDb(billMap);

        Map<String, Object> resultMap = new HashMap<>();
        if (failedRowNumbers.size() > 0) {
            resultMap.put("errorRows", failedRowNumbers);
        }
        return resultMap;
    }

    @Override
    public PMBill get(String userName, String mobile, Date releaseTime) {
        Assert.hasLength(userName, "UserName must not be empty");
        Assert.hasLength(mobile, "UserName must not be empty");
        Assert.notNull(releaseTime, "releaseTime must not be null");
        return billRepository.findByUserNameAndUserMobileAndReleaseTime(userName, mobile, releaseTime);
    }

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     *
     * Private Methods
     *
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    private String getStringCellValue(Sheet sheet, int row, int col) {
        Cell cell = sheet.getRow(row).getCell(col);
        if (cell == null) {
            return null;
        }
        return cell.getStringCellValue();
    }

    private Date getDateCellValue(Sheet sheet, int row, int col) {
        Cell cell = sheet.getRow(row).getCell(col);
        if (cell == null) {
            return null;
        }
        return cell.getDateCellValue();
    }

    private double getNumericCellValue(Sheet sheet, int row, int col) {
        Cell cell = sheet.getRow(row).getCell(col);
        if (cell == null) {
            return 0.0;
        }
        return cell.getNumericCellValue();
    }

    private Map<String, PMBill> parseBillData(Sheet sheet, List<Integer> failedRowNumbers) {
        Map<String, PMBill> billMap = new TreeMap<>();
        int lastRowNum = sheet.getLastRowNum();
        String houseNumber;
        String userName;
        String userMobile;
        String duration;
        Date releaseTime;
        String billRemark;
        String costName;
        BigDecimal unitPrice = null;
        Double number;
        BigDecimal amount = null;
        Date startDay;
        Date endDay;
        String itemRemark;

        // 数据行从1开始
        for (int i = lastRowNum; i > 0; i--) {
            int col = 0;
            houseNumber = getStringCellValue(sheet, i, col++);
            userName = getStringCellValue(sheet, i, col++);
            if (!StringUtils.hasLength(userName)) {
                failedRowNumbers.add(i);
                continue;
            }
            userMobile = getStringCellValue(sheet, i, col++);
            if (!StringUtils.hasLength(userMobile)) {
                failedRowNumbers.add(i);
                continue;
            }
            duration = getStringCellValue(sheet, i, col++);
            releaseTime = getDateCellValue(sheet, i, col++);
            if (releaseTime == null) {
                failedRowNumbers.add(i);
                continue;
            }
            billRemark = getStringCellValue(sheet, i, col++);
            costName = getStringCellValue(sheet, i, col++);
            if (!StringUtils.hasLength(costName)) {
                failedRowNumbers.add(i);
                continue;
            }
            double price = getNumericCellValue(sheet, i, col++);
            if (price > 0) {
                unitPrice = new BigDecimal(price);
            }
            number = getNumericCellValue(sheet, i, col++);
            double am = getNumericCellValue(sheet, i, col++);
            if (am <= 0) {
                failedRowNumbers.add(i);
                continue;
            } else {
                amount = new BigDecimal(am);
            }

            startDay = getDateCellValue(sheet, i, col++);
            if (startDay == null) {
                failedRowNumbers.add(i);
                continue;
            }
            endDay = getDateCellValue(sheet, i, col++);
            if (endDay == null) {
                failedRowNumbers.add(i);
                continue;
            }
            itemRemark = getStringCellValue(sheet, i, col++);

            String key = userName + "_" + userMobile + "_" + DateUtils.of(releaseTime).format(formatter);
            PMBill bill = billMap.get(key);
            if (bill == null) {
                bill = PMBill.of(houseNumber, userName, userMobile, duration, releaseTime, billRemark);
                billMap.put(key, bill);
            }
            PMBillItem item = PMBillItem.of(costName, unitPrice, number, amount, startDay, endDay, itemRemark);
            List<BaseBillItem> l = bill.getBillItems();
            l.add(item);
        }
        return billMap;
    }

    private void saveToDb(Map<String, PMBill> billMap) {
        SysUser sysUser = LoginUtil.loginUser();
        for (String key : billMap.keySet()) {
            String[] sa = key.split("_");
            PMBill bill = this.get(sa[0], sa[1],
                    Date.from(
                            LocalDate.parse(sa[2], formatter).atTime(0, 0, 0)
                                    .toInstant(OffsetDateTime.now().getOffset())
                    )
            );
            PMBill pmBill = billMap.get(key);
            if (bill != null) {
                billItemService.deleteByBill(bill.getId());
                pmBill.setId(bill.getId());
            }
            pmBill.setCreateUserId(sysUser.getId());
            pmBill.setCreateUserPlatformId(sysUser.getPlatformId());
            pmBill.setCreateUserPlatformType(SysConst.SubjectType.PROPERTY);
            this.add(pmBill);
            String billId = pmBill.getId();
            List<BaseBillItem> billItems = pmBill.getBillItems();
            BigDecimal totalAmount = BigDecimal.ZERO;
            for (BaseBillItem billItem : billItems) {
                billItem.setBillId(billId);
                billItemService.add((PMBillItem) billItem);
                totalAmount = totalAmount.add(billItem.getAmount());
            }
            pmBill.setTotalAmount(totalAmount);
            this.update(pmBill);
        }
    }
}